﻿using QuanLyThuVien.DTO;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyThuVien.Supporter;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng PHIEUTRA
    //----------------------------------------------------------------------
    public class DALPhieuTra
    {
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALPhieuTra()
        { }

        //----------------------------------------------------------------------
        //Desc: chuyển dòng dữ liệu thành PHIEUTRA
        //----------------------------------------------------------------------
        public DTOPhieuTra ConvertToPhieuTra(DataRow row)
        {
            DTOPhieuTra phieutra = new DTOPhieuTra();
            phieutra.MaPhieuTra = row["MaPhieuTra"].ToString();
            try
            {
                phieutra.NgayTra = DateTime.Parse(row["NgayTra"].ToString());
            }
            catch { }
            phieutra.DocGia.MaDG = row["MaDG"].ToString();
            try
            {
                phieutra.TongTienPhat = Double.Parse(row["TongTienPhat"].ToString());
            }
            catch { }
            try
            {
                phieutra.TienThuKyNay = Double.Parse(row["TienThuKyNay"].ToString());
            }
            catch { }
            try
            {
                phieutra.ConLai = Double.Parse(row["ConLai"].ToString());
            }
            catch { }

            return phieutra;
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách phiếu trả
        //----------------------------------------------------------------------
        public DataTable LayDanhSachPhieuTra()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spPhieuTra_LayDanhSachPhieuTra";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách phiếu trả của độc giả
        //----------------------------------------------------------------------
        public DataTable LayPhieuTraDocGia(string madocgia)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spPhieuTra_LayPhieuTraDocGia";
            SqlParameter sqlprMaDG = new SqlParameter("@MaDG", SqlDbType.VarChar, 15) { Value = madocgia };
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDG);
        }

        //----------------------------------------------------------------------
        //Desc: lấy phiếu trả
        //----------------------------------------------------------------------
        public DTOPhieuTra LayPhieuTra(string maphieutra)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spPhieuTra_LayPhieuTra";
            SqlParameter sqlprMaPhieuTra = new SqlParameter("@MaPhieuTra", SqlDbType.VarChar, 15) { Value = maphieutra };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaPhieuTra);
            if (data == null || data.Rows.Count == 0)
                return null;
            return ConvertToPhieuTra(data.Rows[0]);
        }

        //----------------------------------------------------------------------
        //Desc: đếm số phiếu trả
        //----------------------------------------------------------------------
        public int DemSoPhieuTra()
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spPhieuTra_DemPhieuTra";
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch
            {
                return -1;
            }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã phiếu trả
        //----------------------------------------------------------------------
        public int DemMaPhieuTra(string maphieutra)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spPhieuTra_DemMaPhieuTra";
            SqlParameter sqlprMaPhieuTra = new SqlParameter("@MaPhieuTra", SqlDbType.VarChar, 15) { Value = maphieutra };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaPhieuTra);

            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch
            {
                return -1;
            }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật phiếu trả
        //----------------------------------------------------------------------
        public bool CapNhat(DTOPhieuTra phieutra)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spPhieuTra_CapNhatPhieuTra";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaPhieuTra = new SqlParameter("@MaPhieuTra", SqlDbType.VarChar, 15) { Value = phieutra.MaPhieuTra };
            SqlParameter sqlprNgayTra = new SqlParameter("@NgayTra", SqlDbType.Date) { Value = phieutra.NgayTra };
            SqlParameter sqlprMaDG = new SqlParameter("@MaDG", SqlDbType.VarChar, 15) { Value = phieutra.DocGia.MaDG };
            SqlParameter sqlprTongTienPhat = new SqlParameter("@TongTienPhat", SqlDbType.Money) { Value = phieutra.TongTienPhat };
            SqlParameter sqlprTienThuKyNay = new SqlParameter("@TienThuKyNay", SqlDbType.Money) { Value = phieutra.TienThuKyNay };
            SqlParameter sqlprConLai = new SqlParameter("@ConLai", SqlDbType.Money) { Value = phieutra.ConLai };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaPhieuTra,
                sqlprNgayTra, sqlprMaDG, sqlprTongTienPhat, sqlprTienThuKyNay, sqlprConLai);
        }

    }
}
